﻿//-----------------------------------------------------------------------
// <copyright file="SqlDbHandler.cs">
//     Copyright (c) Wolf166 - BGF Framework
// </copyright>
// <Autor>Wolf166 - BGF Framework</Autor>
//-----------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BrowserGameFramework;
using System.Data.SqlClient;
using BrowserGameFramework.Common.Configuration;
using System.Data;
using System.Xml;
using System.Xml.Serialization;
using System.IO;
using System.Xml.XPath;
using BrowserGameFramework.Common.XmlHelper;
using BrowserGameFramework.Common.Exceptions;
using BrowserGameFramework.DBConnector.Interfaces;
using BrowserGameFramework.Common.Logging;

namespace BrowserGameFramework.DBConnector.SqlServer
{
    /// <summary>
    /// Basic class for SQL operations with SqlServer.Implemented as singleton pattern.
    /// </summary>
    /// <example>
    /// SqlDbHandler myHandler = SqlDbHandler.GetInstance(); Returns the Object.
    /// You got allways the same instance (So no new login will be send to the Database)
    /// </example>
    public class SqlDbHandler : IDbHandler, IDisposable
    {
        private static SqlDbHandler _instance;

        /// <summary>
        /// Returns an Instance of this Class. Singleton Pattern
        /// </summary>
        /// <returns>An Instance</returns>
        public static SqlDbHandler GetInstance()
        {
            if (_instance == null)
            {
                _instance = new SqlDbHandler();
            }

            return _instance;
        }

        /// <summary>
        /// Sql Connection for current session
        /// </summary>
        private SqlConnection _con = new SqlConnection();

        public SqlConnection Connection
        {
            get
            {
                switch (_con.State)
                {
                    case ConnectionState.Broken:
                        Trace.TraceError("Connection was broken ... try to reopen!");
                        _con = CreateConnection();
                        return _con;
                    case ConnectionState.Closed:
                        _con = CreateConnection();
                        return _con;
                     case ConnectionState.Connecting:
                    case ConnectionState.Executing:
                    case ConnectionState.Fetching:
                    case ConnectionState.Open:
                        return _con;
                    default:
                        throw new BGFBackendException("Unknown connection state" + _con.State.ToString());
                }

            }
            set { _con = value; }
        }

        /// <summary>
        /// Default Constructor
        /// </summary>
        private SqlDbHandler()
        {

        }

        /// <summary>
        /// Calls a Procedure and returns the cursor
        /// </summary>
        /// <param name="cmd">Sqlcommand</param>
        /// <param name="xslt">XSLT for Transformation(Useful if you want to serialize ...)</param>
        /// <returns>XSLT transformed Xmldocument with results</returns>
        public XPathDocument CallProcedureSelect(IDbCommand cmd, XPathDocument xsl)
        {
            XPathDocument xDoc = CallProcedureSelect(cmd);
            string errMsg = string.Empty;
            xDoc = XmlHelper.TransformCompiled(xDoc, xsl, out errMsg);

            if (!string.IsNullOrEmpty(errMsg))
            {
                throw new BGFBackendException("XSLT ERROR on [CallProcedureSelect] " + errMsg);
            }

            return xDoc;
        }

        /// <summary>
        /// Calls a Procedure and returns the cursor
        /// </summary>
        /// <param name="cmd">SqlCommand</param>
        /// <returns>XPathDocument</returns>
        public XPathDocument CallProcedureSelect(IDbCommand cmd)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Connection;

            IDataReader dReader = cmd.ExecuteReader();
            DataTable dt = new DataTable("Table");
            dt.Load(dReader);
            DataSet ds = new DataSet("Results");
            ds.Tables.Add(dt);

            StringReader reader = new StringReader(ds.GetXml());
            XPathDocument xDoc = new XPathDocument(reader);

            return xDoc;
        }

        /// <summary>
        /// Gets a DataTable from SQL Server
        /// </summary>
        /// <param name="cmd">The SqlCommand</param>
        /// <returns>DataTable with result</returns>
        public DataTable CallProcedureDataTableSelect(SqlCommand cmd)
        {
            Trace.TraceEnterEvent("CallProcedureSelect");

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Connection;

            DataTable dt = new DataTable("Table");
            SqlDataReader reader = cmd.ExecuteReader();

            dt.Load(reader);

            Trace.TraceLeaveEvent("CallProcedureSelect");

            return dt;
        }

        /// <summary>
        /// Calls a Procedure and get the xml result from sql server.
        /// The DB Procedure must return xml.
        /// </summary>
        /// <param name="cmd">SqlCommand</param>
        /// <returns>XPathDocument with result</returns>
        public XPathDocument CallXmlProcedureSelect(SqlCommand cmd)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Connection;
            XmlReader reader = cmd.ExecuteXmlReader();
            XPathDocument xDoc = new XPathDocument(reader);

            return xDoc;

        }

        /// <summary>
        /// Calls a Procedure and get the xml result from sql server.
        /// The result will be transformed with xsl.
        /// The DB Procedure must return xml.
        /// </summary>
        /// <param name="cmd">Sqlcommand</param>
        /// <param name="xsl">XPathDocument with XSL</param>
        /// <param name="errMsg">XSL Errors</param>
        /// <exception cref="BGFBackendException"></exception>
        /// <returns></returns>
        public XPathDocument CallXmlProcedureSelect(SqlCommand cmd, XPathDocument xsl)
        {
            XPathDocument xDoc = CallXmlProcedureSelect(cmd);
            string errMsg = string.Empty;

            xDoc = XmlHelper.TransformCompiled(xDoc, xsl, out errMsg);

            if (!string.IsNullOrEmpty(errMsg))
            {
                throw new BGFBackendException("XSLT ERROR ON [CallXmlProcedureSelect]" + errMsg);
            }

            return xDoc;
        }

        /// <summary>
        /// Calls a Procedure and get the returnvalue
        /// </summary>
        /// <param name="cmd">Command</param>
        /// <returns>Value in XmlDocument</returns>
        public XmlDocument CallProcedureExecute(IDbCommand cmd)
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Connection = Connection;
            cmd.ExecuteNonQuery();

            XmlDocument doc = new XmlDocument();
            XmlNode node = doc.CreateNode(XmlNodeType.Element, "Results", "");

            foreach (SqlParameter par in cmd.Parameters)
            {
                if (par.Direction != ParameterDirection.Input)
                {
                    XmlNode child = doc.CreateNode(XmlNodeType.Element, par.ParameterName, "");
                    child.InnerText = par.Value.ToString();
                    node.AppendChild(child);
                }
            }

            doc.AppendChild(node);
            return doc;
        }

        /// <summary>
        /// Calls the Database via SQL
        /// You could use the Procedure Version for more security
        /// </summary>
        /// <param name="cmd">SQL Command</param>
        /// <returns>Xmldocument with result</returns>
        public XPathDocument CallDirectSelect(string sql)
        {
            if (string.IsNullOrEmpty(sql))
            {
                string error = "The SQL Text parameter is empty.";
                Trace.TraceError(error);
                throw new Common.Exceptions.BGFBackendException(error);
            }

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = Connection;

            IDataReader dReader = cmd.ExecuteReader();
            DataTable dt = new DataTable("Table");
            dt.Load(dReader);
            DataSet ds = new DataSet("Results");
            ds.Tables.Add(dt);

            StringReader reader = new StringReader(ds.GetXml());
            XPathDocument xDoc = new XPathDocument(reader);

            return xDoc;
        }

        /// <summary>
        /// Calls the Database via SQL
        /// </summary>
        /// <param name="sql">Sql statement</param>
        /// <returns>Datatable with result</returns>
        public DataTable CallDirectSelectTable(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = Connection;

            SqlDataReader reader = cmd.ExecuteReader();
            DataTable table = new DataTable("Result");
            table.Load(reader);

            return table;
        }

        /// <summary>
        /// Create a new connection
        /// </summary>
        /// <returns>a new connection</returns>
        private SqlConnection CreateConnection()
        {
            Trace.TraceEnterEvent("CreateConnection");

            string connectionString = AppConfMan.GetKeyAsString("BGFDbConnectionString");

            if (string.IsNullOrEmpty(connectionString))
            {
                throw new Common.Exceptions.BGFBackendException("No connectionstring with key BGFDbConnectionString found on webconfig");
            }

            SqlConnection con = new SqlConnection(connectionString);

            con.Open();

            Trace.TraceLeaveEvent("CreateConnection");

            return con;
        }

        /// <summary>
        /// Closes the sqlconnection
        /// </summary>
        private void CloseConnection()
        {
            Trace.TraceEnterEvent("CloseConnection");

            _con.Close();

            Trace.TraceLeaveEvent("CloseConnection");
        }

        #region IDisposable Member

        public void Dispose()
        {
            CloseConnection();
        }

        #endregion
    }
}
